package com.googlecode.yonggemovie.utils.db;

import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;


/**
 * DAO操作基类，其他DAO继承此类使用
 * @author Kobe Su,2009-12-4
 */
public class BaseDao {

	private Log log = LogFactory.getLog(BaseDao.class);
	
	private static final String QUESTION_MARK = "?";
	
	
	//映射单行结果集
    private class SingleResultSetExtractor implements ResultSetExtractor {
        private SingleRowMapper singleRowMapper = null;

        public SingleResultSetExtractor(SingleRowMapper singleRowMapper) {
            this.singleRowMapper = singleRowMapper;
        }

        public Object extractData(ResultSet rs) throws SQLException {
            if (rs.next()) {
                return singleRowMapper.mapRow(rs);
            }
            return null;
        }
    }
	
	//映射多行结果集
	private class MultiResultSetExtractor implements ResultSetExtractor {

        private MultiRowMapper multiRowMapper = null;

        public MultiResultSetExtractor(MultiRowMapper multiRowMapper) {
            this.multiRowMapper = multiRowMapper;
        }

        public Object extractData(ResultSet rs) throws SQLException {
            ArrayList<Object> data = new ArrayList<Object>();
            while (rs.next()) {
                data.add(multiRowMapper.mapRow(rs));
            }
            return data;
        }
    }
	
	//映射map形式结果集
    private class MapResultExtractor implements ResultSetExtractor {

        private MapRowMapper rowMapper = null;

        public MapResultExtractor(MapRowMapper rowMapper) {
            this.rowMapper = rowMapper;
        }

        public Object extractData(ResultSet rs) throws SQLException {
            Map<Object,Object> results = new HashMap<Object,Object>();
            while (rs.next()) {
                results.put(rowMapper.mapRowKey(rs), rowMapper.mapRowValue(rs));
            }

            return results;
        }
    }
	
	
    /**
     * 获取单行结果集对应的对象.
     * 
     * @param sql sql语句
     * @param singleRowMapper 处理单行结果集的接口
     * @return 结果对象，如果没有记录返回null
     */
    protected Object query(String sql, SingleRowMapper singleRowMapper) {
    	return this.query(sql, null, singleRowMapper);
    }
	
    /**
     * 获取单行结果集对应的对象.
     * 
     * @param sql sql语句
     * @param args 参数数组
     * @param singleRowMapper 处理单行结果集的接口
     * @return 结果对象，如果没有记录返回null
     */
    protected Object query(String sql, Object[] args, SingleRowMapper singleRowMapper) {
    	log.debug(getSQL(sql, args));
    	
    	Connection conn = Database.getConnection();
    	PreparedStatement ps = null;
    	ResultSet rs = null;
		Object obj = null;
    	try {
			ps = conn.prepareStatement(sql);
			setPreparedSQLArgs(ps, args);
			rs = ps.executeQuery();
			
			obj = new SingleResultSetExtractor(singleRowMapper).extractData(rs);
				
		} catch (SQLException e) {
			log.debug(e.getMessage(), e);
		} finally {
			closeStatement(ps);
			closeResultSet(rs);
		}	
    	
    	return obj;
    }
	
    /**
     * 获取多行结果对象列表.
     * 
     * @param sql sql语句
     * @param multiRowMapper 处理多行结果集的接口
     * @return 结果对象列表
     */
	protected List queryForList(String sql, MultiRowMapper multiRowMapper) {
		return queryForList(sql, null,multiRowMapper);
	}
	
    /**
     * 获取多行结果对象列表.
     * 
     * @param sql sql语句
     * @param multiRowMapper 处理多行结果集的接口
     * @return 结果对象列表
     */
    protected List queryForList(String sql, Object[] args, MultiRowMapper multiRowMapper) {
    	log.debug(getSQL(sql, args));
    	
    	Connection conn = Database.getConnection();
    	PreparedStatement ps = null;
    	ResultSet rs = null;
		List list = new ArrayList();
    	try {
			ps = conn.prepareStatement(sql);
			setPreparedSQLArgs(ps, args);
			rs = ps.executeQuery();
			
			list = (List)new MultiResultSetExtractor(multiRowMapper).extractData(rs);
				
		} catch (SQLException e) {
			log.debug(e.getMessage(), e);
		} finally {
			closeStatement(ps);
			closeResultSet(rs);
		}	
    	
    	return list;
    }
    
    /**
     * 获取结果集的map.
     * 
     * @param sql sql语句
     * @param mapRowMapper 处理多行记录集的接口,指定map的key和value
     * @return 结果对象的map
     */
    protected Map queryForMap(String sql,MapRowMapper mapRowMapper) {
    	return this.queryForMap(sql, null, mapRowMapper);
    }
    
    
    /**
     * 获取结果集的map.
     * 
     * @param sql sql语句
     * @param args 参数数组
     * @param mapRowMapper 处理多行记录集的接口,指定map的key和value
     * @return 结果对象的map
     */
    protected Map queryForMap(String sql, Object[] args, MapRowMapper mapRowMapper) {
    	log.debug(getSQL(sql, args));
    	
    	Connection conn = Database.getConnection();
    	PreparedStatement ps = null;
    	ResultSet rs = null;
		Map map = null;
    	try {
			ps = conn.prepareStatement(sql);
			setPreparedSQLArgs(ps, args);
			rs = ps.executeQuery();
			
			map = (Map)new MapResultExtractor(mapRowMapper).extractData(rs);
				
		} catch (SQLException e) {
			log.debug(e.getMessage(), e);
		} finally {
			closeStatement(ps);
			closeResultSet(rs);
		}	
    	
    	return map;
    	
    }
    
    /**
     * 更新数据.
     * 
     * @param sql sql语句
     * @return 更新的记录条数
     */
    protected int update(String sql) {
    	return this.update(sql, null);
    }
    
    
    /**
     * 更新数据.
     * 
     * @param sql sql语句
     * @param args 参数数组
     * @return 更新的记录条数
     */
    protected int update(String sql, Object[] args) {
    	log.debug(getSQL(sql, args));
    	
    	Connection conn = Database.getConnection();
    	PreparedStatement ps = null;
    	int affectedRowCount = -1;
    	try {
			ps = conn.prepareStatement(sql);
			setPreparedSQLArgs(ps, args);
			affectedRowCount = ps.executeUpdate();
			
		} catch (SQLException e) {
			log.debug(e.getMessage(), e);
		} finally {
			closeStatement(ps);
		}

    	return affectedRowCount;
    }
    
    /**
     * 批量更新数据.
     * 
     * @param sql sql语句
     * @param listOfArgs 参数数组的List集合
     * @return 更新的记录条数数组
     */
    protected int[] batchUpdate(String sql, List<Object[]> listOfArgs, int[] argTypes) {
        for (Object[] args : listOfArgs) {
            log.debug(getSQL(sql, args));
        }
    	
    	Connection conn = Database.getConnection();
    	PreparedStatement ps = null;
    	int[] affectedRowCount = null;
    	try {
    		ps = conn.prepareStatement(sql);
    		DatabaseMetaData dbmd = conn.getMetaData();
    		if(dbmd.supportsBatchUpdates()) {
    			for (Object[] args : listOfArgs) {
    				setPreparedSQLArgs(ps, args);
    				ps.addBatch();
    			}
    			affectedRowCount = ps.executeBatch();
    		}else {
    			
    			int[] affectedRows = new int[listOfArgs.size()];
    			int i=0;
    			for (Object[] args : listOfArgs) {
    				setPreparedSQLArgs(ps, args);
    				affectedRows[i++] = ps.executeUpdate();
    			}
    			affectedRowCount = affectedRows;
    		}
			
		} catch (SQLException e) {
			log.debug(e.getMessage(), e);
		} finally {
			closeStatement(ps);
		}

    	return affectedRowCount;
    }
    
    
    
    
    
    
    
    //设置preparedSQL的参数值
    private void setPreparedSQLArgs(PreparedStatement ps,Object[] args) throws SQLException {
    	if(args == null || args.length == 0)
    		return;
    	
		for(int i=0;i<args.length;i++) {
			Object value = args[i];
			int paramIndex = i+1;
			if (value instanceof StringBuffer || value instanceof StringWriter) {
				ps.setString(paramIndex, value.toString());
			}
			else if ((value instanceof java.util.Date) && !(value instanceof java.sql.Date ||
					value instanceof java.sql.Time || value instanceof java.sql.Timestamp)) {
				ps.setTimestamp(paramIndex, new java.sql.Timestamp(((java.util.Date) value).getTime()));
			}
			else if (value instanceof Calendar) {
				Calendar cal = (Calendar) value;
				ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()));
			}
			else {
				ps.setObject(paramIndex, value);
			}
		}
    }
    
    
    /**
     * 关闭Statement
     */
	protected void closeStatement(Statement st) {
		if(st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			st = null;
		}
	}

    /**
     * 关闭ResultSet
     */
	protected void closeResultSet(ResultSet rs) {
		if(rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}
	}
    
    
    /**
     * 取得填充参数后的sql
     * 
     * @param preparedSQL 预编译sql
     * @param args 参数数组
     * @return 填充参数后的sql
     */
    private String getSQL(String preparedSQL, Object[] args) {
        if (args == null || args.length == 0) {
            return preparedSQL;
        }

        StringBuffer sql = new StringBuffer();

        int index = 0;
        int parameterIndex = 0;

        while ((index = preparedSQL.indexOf(QUESTION_MARK)) > 0) {
            sql.append(preparedSQL.substring(0, index));
            preparedSQL = preparedSQL.substring(index + 1);

            Object arg = args[parameterIndex++];

            if (arg == null) {
                sql.append("null");
            }
            else if (arg instanceof String) {
                sql.append("'");
                sql.append(arg);
                sql.append("'");
            }
            else if (arg instanceof java.util.Date) {
                sql.append("'");
                sql.append(date2String((java.util.Date) arg));
                sql.append("'");
            }
            else {
                sql.append(arg);
            }
        }

        sql.append(preparedSQL);

        return sql.toString();
    }
    
    
    /**
     * 把时间转换成字符串，格式为2006-01-10 20:56:30.756
     * 
     * @param date 时间
     * @return 时间字符串
     */
    public  String date2String(Date date) {
        return date2String(date, "yyyy-MM-dd HH:mm:ss.SSS");
    }
    
    /**
     * 按照指定格式把时间转换成字符串，格式的写法类似yyyy-MM-dd HH:mm:ss.SSS
     * 
     * @param date 时间
     * @param pattern 格式
     * @return 时间字符串
     */
    public String date2String(Date date, String pattern) {
        if (date == null) {
            return null;
        }
        return (new SimpleDateFormat(pattern)).format(date);
    }
    
    
    
    
    
    
    
    
    
    
}
